package edu.uba.fcen.estimacion.database;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStreamReader;
import java.io.Reader;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

import org.apache.log4j.Logger;

import edu.uba.fcen.estimacion.word.selection.constants.Constants;

public class DataBaseBuilderWords {

	private static final Logger logger = Logger.getLogger(DataBaseBuilderWords.class);
	private Connection connection;
	private String pathToCSV;
	
	public DataBaseBuilderWords(String pathToCsv) {
		try {
			this.pathToCSV = pathToCsv;
			Class.forName("org.hsqldb.jdbcDriver");
			this.connection = DriverManager.getConnection("jdbc:hsqldb:mem:words;shutdown=true", "sa", "");
			logger.debug("The default schema was deleted");
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}
	
	private void addData(final int topK) throws Exception {
		BufferedReader bufferReader;
		InputStreamReader files = new InputStreamReader(new FileInputStream(this.pathToCSV), Constants.ENCODING);
//		InputStreamReader files = new InputStreamReader(this.getClass().getResourceAsStream(this.pathToCSV), Constants.ENCODING);
		Statement st = this.connection.createStatement();
		String line;
		//try {
		bufferReader = new BufferedReader(files);
		String command = "INSERT INTO words VALUES (";
		String[] values;
		int index = 1;
		while ((line = bufferReader.readLine()) != null && index <= topK) {
			try{
				values = line.split(";");
				String exec = command + index + ",'"+ values[0] + "'," + values[1] + "," + values[2]
						+ "," + values[3] + "," + values[4] + "," + values[5]
						+ "," + values[6] + ")";
				st.executeQuery(exec);
				index++;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	private void createDB() {
		BufferedReader bufferReader;
		try {
			Reader in = new InputStreamReader(this.getClass().getResourceAsStream("/hsqldb/wordsDB.sql"), Constants.ENCODING);
			bufferReader = new BufferedReader(in);
			StringBuffer text = new StringBuffer();
			String line;
			while ((line = bufferReader.readLine()) != null) {
				text.append(line);
			}
			String[] commands = text.toString().split(";");
			for (String command : commands) {
				CallableStatement prepareCall;
				try {
					prepareCall = this.connection.prepareCall(command);
					prepareCall.execute();
					if (logger.isDebugEnabled()) {
						logger.debug("Executed: " + command);
					}
				} catch (SQLException e) {
					logger.error("Exception because table already exist");
				}
			}
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		
	}
	
	private void restoreDB() {
        try {   // Restore the database
        	Statement st = this.connection.createStatement();
            st.executeQuery("DELETE FROM words");
            logger.info("* Restore the original databases, removing all previous inserting data.");
        } catch (Exception e) {  
            logger.error("Warning: error on vacuum tables: " + e.getMessage());   
        } 
    }
	
	public Connection create(int topK) {
		this.createDB();
		try {
			this.restoreDB();
			this.addData(topK);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return this.connection;
	}
}
